<?php

namespace App\Services\Tools;

use App\Response\Response;
use App\Exceptions\BasicException;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;


class Export
{
    /**
     * @param string $desc 导出的文件的部分文件名
     * @param array $fields 导出表头信息
     * @param array $data  导出的值
     * @param  string  $mergeCell  要合并的单元格
     * @return string 表格Excel，OSS文件地址
     * @throws BasicException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public static function exportUsePhpOffice($desc, $fields, $data, $mergeCell = '')
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $letters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

        if ($mergeCell) {
            if (is_array($mergeCell)) {
                foreach ($mergeCell as $value) {
                    //合并单元格
                    $spreadsheet->getActiveSheet()->mergeCells($value['location']);
                    $sheet->setCellValue(substr($value['location'], 0, strrpos($value['location'], ":")), $value['text']);
                    //设置水平居中
                    $styleArray = [
                        'alignment' => [
                            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                        ],
                    ];
                    $sheet->getStyle($value['location'])->applyFromArray($styleArray);
                }
            } else {
                //合并单元格
                $spreadsheet->getActiveSheet()->mergeCells($mergeCell);
                $sheet->setCellValue('A1', '支付宝批量付款文件模板（前面两行请勿删除）');
                //设置水平居中
                $styleArray = [
                    'alignment' => [
                        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    ],
                ];
                $sheet->getStyle('A1')->applyFromArray($styleArray);
            }
        }

        if ($mergeCell) {
            $titleStartRowNo = 2;
        } else {
            $titleStartRowNo = 1;
        }

        //设置第一行小标题
        $k = 0;
        foreach ($fields as $field) {
            //设置列宽
            $sheet->getColumnDimension($letters[$k])->setWidth($field['width']);
            //设置单元格的值
            $sheet->setCellValue($letters[$k] . $titleStartRowNo, $field['header']);
            $k++;
        }

        //循环赋值
        $rowIndex = $titleStartRowNo + 1;
        foreach ($data as $k => $v) {
            $colIndex = 0;
            foreach ($fields as $field) {
                $fieldKey = $field['field'];
                $sheet->setCellValue($letters[$colIndex] . $rowIndex, $v[$fieldKey]);
                $colIndex++;
            }
            $rowIndex++;
        }

        $filename = $desc . '-' . date('Ymd-His') . '-' . mt_rand(10, 99) . app('request')->admin_id . '.xls';
        //保存到本地
        $writer = new Xls($spreadsheet);
        $localFile = storage_path('app/') . $filename;
        $writer->save($localFile);
        //上传到oss
        $ossFile = 'export_data/' . $filename;
        $uploadRes = ObsService::putObs($ossFile,$localFile);
//        $uploadRes = Storage::disk('oss')->put($ossFile, file_get_contents($localFile));
        if ($uploadRes) {
            unlink($localFile); //删除本地文件
            return env('OBS_HOST') .'/'. $ossFile;
        } else {
            throw new BasicException(0, '导出失败');
        }
    }

    /** 批量离线 导出到本地文件
     *
     * @param  string  $dirName  这批导出的文件放置的文件地址
     * @param $desc 文件名前缀
     * @param $fields 各个列名字以及字段属性
     * @param $data 数据（二维数组)
     * @param  string  $mergeCell
     * @return array
     */
    public function toExcelLocalFiles(string $dirName, $desc, $fields, $data, $mergeCell = '')
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $letters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

        if ($mergeCell) {
            if (is_array($mergeCell)) {
                foreach ($mergeCell as $value) {
                    //合并单元格
                    $spreadsheet->getActiveSheet()->mergeCells($value['location']);
                    $sheet->setCellValue(substr($value['location'], 0, strrpos($value['location'], ":")), $value['text']);
                    //设置水平居中
                    $styleArray = [
                        'alignment' => [
                            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                        ],
                    ];
                    $sheet->getStyle($value['location'])->applyFromArray($styleArray);
                }
            } else {
                //合并单元格
                $spreadsheet->getActiveSheet()->mergeCells($mergeCell);
                $sheet->setCellValue('A1', '支付宝批量付款文件模板（前面两行请勿删除）');
                //设置水平居中
                $styleArray = [
                    'alignment' => [
                        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    ],
                ];
                $sheet->getStyle('A1')->applyFromArray($styleArray);
            }
        }

        //设置第一行小标题
        $k = 0;
        foreach ($fields as $field) {
            //设置列宽
            $sheet->getColumnDimension($letters[$k])->setWidth($field['width']);
            //设置单元格的值
            $sheet->setCellValue($letters[$k] . 2, $field['header']);
            $k++;
        }
        $k = 0;
        foreach ($fields as $field) {
            //设置列宽
            $sheet->getColumnDimension($letters[$k])->setWidth($field['width']);
            //设置单元格的值
            $sheet->setCellValue($letters[$k] . 2, $field['header']);
            $k++;
        }
        var_dump('111111111111:' . memory_get_usage() . "\n");
        //循环赋值
        $rowIndex = 3;
        foreach ($data as $k => $v) {
            $colIndex = 0;
            foreach ($fields as $field) {
                $fieldKey = $field['field'];
                $sheetUnit = $sheet->getCell($letters[$colIndex] . $rowIndex);
                $action = $sheetUnit->setValue($v[$fieldKey]);
                unset($sheetUnit);
                unset($action);
                $colIndex++;
            }
            $rowIndex++;
        }

        unset($sheet);
        unset($data);
        var_dump('1111111111:' . memory_get_usage() . "\n");
        $filename = $desc . '-' . date('Ymd-His') . '-' . mt_rand(10, 99) . app('request')->admin_id . '.xlsx';
        //保存到本地

        $writer = new Xlsx($spreadsheet);

        if (!file_exists(storage_path('excel'))) {
            mkdir(storage_path('excel'));
        }
        if (!file_exists($dirName)) {
            mkdir($dirName);
        }
        $localFile = $dirName . $filename;

        $writer->save($localFile);
        unset($writer);
        unset($spreadsheet);

        return $localFile;
    }


    /** 批量离线 导出到本地文件
     *
     * @param  string  $dirName  这批导出的文件放置的文件地址
     * @param $desc 文件标识
     * @param $tableHead 一维维数组['index_name'=>value]
     * @param $data  二维数组 {['index_name'=>value,'index_name2'=>value2],['index_name'=>value,'index_name2'=>value2]]
     * @return array
     */
    public function writeCsv(string $dirName, string $desc, $tableHead, $data)
    {
        if (!file_exists(storage_path('excel'))) {
            mkdir(storage_path('excel'));
        }
        if (!file_exists($dirName)) {
            mkdir($dirName);
        }
        $fileName = $desc . '-' . date('Ymd-His') . '-' . mt_rand(10, 99) . app('request')->admin_id . '.csv';
//        foreach ($tableHead as $key => $v) {//
//            //以gbk的编码方式编码，防止乱码
//            $b_a[$key] = mb_convert_encoding($v, "gbk");
//        }
        $fh = fopen($dirName . $fileName, 'w');
        if (!$fh) {
            return false;
        }
        fwrite($fh,chr(0xEF).chr(0xBB).chr(0xBF));//输出BOM头
        array_unshift($data,$tableHead);
        foreach ($data as $k => $a) {
            if (fputcsv($fh, $a) === false) {
                return false;
            }
        }
        fclose($fh);

        return $dirName . $fileName;
    }


}
